&ANALYZE-SUSPEND _VERSION-NUMBER UIB_v9r12 GUI
&ANALYZE-RESUME
&Scoped-define WINDOW-NAME dbbr
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _DEFINITIONS dbbr 
/*------------------------------------------------------------------------
    File        : dbbrowser.w
    Purpose     : Demonstrates the use of the dynamic database objects.

    Author(s)   : Bruce Gruenbaum
    
    Notes       : This file contains sample code which may assist you in 
                  creating applications.  You may use the code as you see 
                  fit. If you modify the code or include it in another 
                  software program, you will refrain from identifying 
                  Progress Software as the supplier of the code, or using 
                  any Progress Software trademarks in connection with 
                  your use of the code. 
                  
                  THE CODE IS NOT SUPPORTED BY PROGRESS SOFTWARE AND IS 
                  PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
                  INCLUDING, WITHOUT LIMITATION, ANY WARRANTY OF 
                  MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR 
                  NONINFRINGEMENT.
------------------------------------------------------------------------*/
/*          This .W file was created with the Progress AppBuilder.      */
/*----------------------------------------------------------------------*/

/* Create an unnamed pool to store all the widgets created 
     by this procedure. This is a good default which assures
     that this procedure's triggers and internal procedures 
     will execute in this procedure's storage, and that proper
     cleanup will occur on deletion of the procedure. */

CREATE WIDGET-POOL.

/* ***************************  Definitions  ************************** */

/* Parameters Definitions ---                                           */

/* Local Variable Definitions ---                                       */

/* Define a handle for the dynamic browse */
DEFINE VARIABLE hBrowse AS HANDLE NO-UNDO.

/* Define a handle for the query for the browse */
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.


/* Static toggle switch for the line color */
DEFINE VARIABLE lRow AS LOGICAL NO-UNDO.

/* List of column handles for the browse */
DEFINE VARIABLE cBrwsCols AS CHARACTER NO-UNDO.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&ANALYZE-SUSPEND _UIB-PREPROCESSOR-BLOCK 

/* ********************  Preprocessor Definitions  ******************** */

&Scoped-define PROCEDURE-TYPE Window
&Scoped-define DB-AWARE no

/* Name of first Frame and/or Browse and/or first Query                 */
&Scoped-define FRAME-NAME DEFAULT-FRAME

/* Standard List Definitions                                            */
&Scoped-Define ENABLED-OBJECTS cFields cDatabase lAppSrvr btn-Refresh ~
cTable fiWhere cOperator fiValue btn-GetTT btn-Query RECT-1 
&Scoped-Define DISPLAYED-OBJECTS cFields cDatabase lAppSrvr cTable fiWhere ~
cOperator fiValue 

/* Custom List Definitions                                              */
/* List-1,List-2,List-3,List-4,List-5,List-6                            */

/* _UIB-PREPROCESSOR-BLOCK-END */
&ANALYZE-RESUME


/* ************************  Function Prototypes ********************** */

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD cleanupQuery dbbr 
FUNCTION cleanupQuery RETURNS LOGICAL
  ( INPUT hQuery AS HANDLE )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD deleteObject dbbr 
FUNCTION deleteObject RETURNS LOGICAL
  ( INPUT ihObject AS HANDLE )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD getFieldValue dbbr 
FUNCTION getFieldValue RETURNS CHARACTER
  ( INPUT icField AS CHARACTER,
    INPUT ihQuery AS HANDLE )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD listTempTables dbbr 
FUNCTION listTempTables RETURNS CHARACTER
  ( /* parameter-definitions */ )  FORWARD.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


/* ***********************  Control Definitions  ********************** */

/* Define the widget handle for the window                              */
DEFINE VAR dbbr AS WIDGET-HANDLE NO-UNDO.

/* Definitions of the field level widgets                               */
DEFINE BUTTON btn-GetTT 
     LABEL "Get Temp Table" 
     SIZE 11.67 BY 1.17.

DEFINE BUTTON btn-Query 
     LABEL "Open Query" 
     SIZE 12.67 BY 1.17.

DEFINE BUTTON btn-Refresh 
     LABEL "Refresh DB List" 
     SIZE 11 BY 1.17.

DEFINE VARIABLE cDatabase AS CHARACTER FORMAT "X(30)":U 
     LABEL "Database" 
     VIEW-AS COMBO-BOX SORT INNER-LINES 5
     LIST-ITEMS "Item 1" 
     DROP-DOWN-LIST
     SIZE 16.67 BY 1 NO-UNDO.

DEFINE VARIABLE cOperator AS CHARACTER FORMAT "X(256)":U INITIAL "=" 
     VIEW-AS COMBO-BOX INNER-LINES 5
     LIST-ITEMS "=",">=","<=","Begins","Matches" 
     DROP-DOWN-LIST
     SIZE 7.67 BY 1 NO-UNDO.

DEFINE VARIABLE cTable AS CHARACTER FORMAT "X(32)":U 
     LABEL "Table" 
     VIEW-AS COMBO-BOX SORT INNER-LINES 5
     LIST-ITEM-PAIRS "Item 1","Item 1"
     DROP-DOWN-LIST
     SIZE 16.67 BY 1 NO-UNDO.

DEFINE VARIABLE fiValue AS CHARACTER FORMAT "X(256)":U 
     VIEW-AS FILL-IN 
     SIZE 12 BY 1.11 NO-UNDO.

DEFINE VARIABLE fiWhere AS CHARACTER FORMAT "X(256)":U 
     LABEL "Where Clause" 
     VIEW-AS FILL-IN 
     SIZE 17 BY 1.11 NO-UNDO.

DEFINE RECTANGLE RECT-1
     EDGE-PIXELS 2 GRAPHIC-EDGE  NO-FILL 
     SIZE 29.33 BY 6.33.

DEFINE VARIABLE cFields AS CHARACTER 
     VIEW-AS SELECTION-LIST MULTIPLE SCROLLBAR-VERTICAL 
     SIZE 27.45 BY 5.5 NO-UNDO.

DEFINE VARIABLE lAppSrvr AS LOGICAL INITIAL no 
     LABEL "AppServer" 
     VIEW-AS TOGGLE-BOX
     SIZE 9 BY .78 NO-UNDO.


/* ************************  Frame Definitions  *********************** */

DEFINE FRAME DEFAULT-FRAME
     cFields AT ROW 1.44 COL 51 NO-LABEL
     cDatabase AT ROW 1.56 COL 9.33 COLON-ALIGNED
     lAppSrvr AT ROW 1.56 COL 29.56
     btn-Refresh AT ROW 2.67 COL 28.89
     cTable AT ROW 2.72 COL 9.33 COLON-ALIGNED
     fiWhere AT ROW 4.33 COL 9 COLON-ALIGNED
     cOperator AT ROW 4.33 COL 26.33 COLON-ALIGNED NO-LABEL
     fiValue AT ROW 4.39 COL 34.45 COLON-ALIGNED NO-LABEL
     btn-GetTT AT ROW 6 COL 16
     btn-Query AT ROW 6 COL 29
     RECT-1 AT ROW 1.06 COL 50
    WITH 1 DOWN NO-BOX KEEP-TAB-ORDER OVERLAY 
         SIDE-LABELS NO-UNDERLINE THREE-D 
         AT COL 1 ROW 1
         SIZE 80 BY 30.28.


/* *********************** Procedure Settings ************************ */

&ANALYZE-SUSPEND _PROCEDURE-SETTINGS
/* Settings for THIS-PROCEDURE
   Type: Window
   Allow: Basic,Browse,DB-Fields,Window,Query
   Other Settings: COMPILE
 */
&ANALYZE-RESUME _END-PROCEDURE-SETTINGS

/* *************************  Create Window  ************************** */

&ANALYZE-SUSPEND _CREATE-WINDOW
IF SESSION:DISPLAY-TYPE = "GUI":U THEN
  CREATE WINDOW dbbr ASSIGN
         HIDDEN             = YES
         TITLE              = "Database Browser"
         HEIGHT             = 30.28
         WIDTH              = 80
         MAX-HEIGHT         = 40.06
         MAX-WIDTH          = 157.22
         VIRTUAL-HEIGHT     = 40.06
         VIRTUAL-WIDTH      = 157.22
         MAX-BUTTON         = no
         RESIZE             = no
         SCROLL-BARS        = no
         STATUS-AREA        = no
         BGCOLOR            = ?
         FGCOLOR            = ?
         KEEP-FRAME-Z-ORDER = yes
         THREE-D            = yes
         MESSAGE-AREA       = no
         SENSITIVE          = yes.
ELSE {&WINDOW-NAME} = CURRENT-WINDOW.
/* END WINDOW DEFINITION                                                */
&ANALYZE-RESUME



/* ***********  Runtime Attributes and AppBuilder Settings  *********** */

&ANALYZE-SUSPEND _RUN-TIME-ATTRIBUTES
/* SETTINGS FOR WINDOW dbbr
  VISIBLE,,RUN-PERSISTENT                                               */
/* SETTINGS FOR FRAME DEFAULT-FRAME
                                                                        */
IF SESSION:DISPLAY-TYPE = "GUI":U AND VALID-HANDLE(dbbr)
THEN dbbr:HIDDEN = no.

/* _RUN-TIME-ATTRIBUTES-END */
&ANALYZE-RESUME

 



/* ************************  Control Triggers  ************************ */

&Scoped-define SELF-NAME dbbr
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL dbbr dbbr
ON END-ERROR OF dbbr /* Database Browser */
OR ENDKEY OF {&WINDOW-NAME} ANYWHERE DO:
  /* This case occurs when the user presses the "Esc" key.
     In a persistently run window, just ignore this.  If we did not, the
     application would exit. */
  IF THIS-PROCEDURE:PERSISTENT THEN RETURN NO-APPLY.
END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL dbbr dbbr
ON WINDOW-CLOSE OF dbbr /* Database Browser */
DO:
    APPLY "CLOSE":U TO THIS-PROCEDURE.
    RETURN NO-APPLY.
END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&Scoped-define SELF-NAME btn-GetTT
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL btn-GetTT dbbr
ON CHOOSE OF btn-GetTT IN FRAME DEFAULT-FRAME /* Get Temp Table */
DO:
  RUN getTempTable.
END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&Scoped-define SELF-NAME btn-Query
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL btn-Query dbbr
ON CHOOSE OF btn-Query IN FRAME DEFAULT-FRAME /* Open Query */
DO:
  RUN getBrowse.
END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&Scoped-define SELF-NAME btn-Refresh
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL btn-Refresh dbbr
ON CHOOSE OF btn-Refresh IN FRAME DEFAULT-FRAME /* Refresh DB List */
DO:
  RUN getDatabaseList.
END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&Scoped-define SELF-NAME cDatabase
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL cDatabase dbbr
ON VALUE-CHANGED OF cDatabase IN FRAME DEFAULT-FRAME /* Database */
DO:

  /* Just want to make sure that the variable and screen-value are in sync */
  ASSIGN {&SELF-NAME}.

  /* If the database combo has nothing selected, there's nothing you can do */
  CASE {&SELF-NAME}:
    WHEN "":U OR
    WHEN ? THEN
     ASSIGN 
       cTable:LIST-ITEM-PAIRS = ?
       cTable:SCREEN-VALUE = ""
     .
    WHEN "<Dynamic Temp Tables>" THEN
    DO:
      RUN getTempTableList.
    END.
    OTHERWISE
    DO:
      /* Get the list items for the cTable combo-box */
      RUN getListItems ({&SELF-NAME} + "._File",
                        "FOR EACH _File NO-LOCK WHERE NOT _File._Hidden",
                        "_File._File-Name",
                        "_File._File-Name",
                        cTable:HANDLE).
    END.
  END.

  /* Now make sure that the Fields selection list is properly populated */
  APPLY "VALUE-CHANGED" TO cTable.
  
END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&Scoped-define SELF-NAME cFields
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL cFields dbbr
ON LEFT-MOUSE-DBLCLICK OF cFields IN FRAME DEFAULT-FRAME
DO:
  
  ASSIGN fiWhere:SCREEN-VALUE = cFields:SCREEN-VALUE.
END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&Scoped-define SELF-NAME cTable
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL cTable dbbr
ON VALUE-CHANGED OF cTable IN FRAME DEFAULT-FRAME /* Table */
DO:
  
  /* Just want to make sure that the variable and screen-value are in sync */
  ASSIGN {&SELF-NAME}.

  /* If the database combo has nothing selected, there's nothing you can do */
  IF {&SELF-NAME} = "":U OR
     {&SELF-NAME} = ? THEN
     ASSIGN 
       cFields:LIST-ITEM-PAIRS = ?
       cFields:SCREEN-VALUE = ""
     .
  ELSE
  DO:
    IF cDatabase:SCREEN-VALUE = "<Dynamic Temp Tables>" THEN
    DO:
      RUN getTempTableFields.
    END.
    ELSE
      /* Get the list items for the cFields selection list */
      RUN getListItems (cDatabase + "._File," + cDatabase + "._Field",
                        "FOR EACH _File NO-LOCK WHERE _File._File-name = '" + cTable +
                        "', EACH _Field NO-LOCK WHERE _Field._File-recid = RECID(_File) AND NOT _Field._sys-field BY _order",
                        "_Field._Field-Name",
                        "_Field._Field-Name",
                        cFields:HANDLE).
  END.

  /* Make sure the browse has nothing in it */
  RUN emptyBrowse.
END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&Scoped-define SELF-NAME lAppSrvr
&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CONTROL lAppSrvr dbbr
ON VALUE-CHANGED OF lAppSrvr IN FRAME DEFAULT-FRAME /* AppServer */
DO:
  ASSIGN
    {&SELF-NAME}
  .
END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


&UNDEFINE SELF-NAME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _MAIN-BLOCK dbbr 


/* ***************************  Main Block  *************************** */

/* Set CURRENT-WINDOW: this will parent dialog-boxes and frames.        */
ASSIGN CURRENT-WINDOW                = {&WINDOW-NAME} 
       THIS-PROCEDURE:CURRENT-WINDOW = {&WINDOW-NAME}.

SESSION:SET-WAIT-STATE( "" ).
/* The CLOSE event can be used from inside or outside the procedure to  */
/* terminate it.                                                        */
ON CLOSE OF THIS-PROCEDURE 
   RUN disable_UI.

/* Best default for GUI applications is...                              */
PAUSE 0 BEFORE-HIDE.

/* Now enable the interface and wait for the exit condition.            */
/* (NOTE: handle ERROR and END-KEY so cleanup code will always fire.    */
MAIN-BLOCK:
DO ON ERROR   UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK
   ON END-KEY UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK:
  RUN initializeObject.
  RUN enable_UI.
  IF NOT THIS-PROCEDURE:PERSISTENT THEN
    WAIT-FOR WINDOW-CLOSE OF THIS-PROCEDURE.
END.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME


/* **********************  Internal Procedures  *********************** */

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE disable_UI dbbr 
PROCEDURE disable_UI :
/*------------------------------------------------------------------------------
  Purpose:     DISABLE the User Interface
  Parameters:  <none>
  Notes:       Here we clean-up the user-interface by deleting
               dynamic widgets we have created and/or hide 
               frames.  This procedure is usually called when
               we are ready to "clean-up" after running.
------------------------------------------------------------------------------*/
  /* Delete the WINDOW we created */
IF THIS-PROCEDURE:PERSISTENT THEN DELETE PROCEDURE THIS-PROCEDURE.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE emptyBrowse dbbr 
PROCEDURE emptyBrowse :
/*------------------------------------------------------------------------------
  Purpose:     To uncouple the query from the browse, and get rid of all the
               dynamic objects used by the query.
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
    /* Uncouple the query from the browse */
    hBrowse:QUERY = ?.

    /* If the handle in hQuery is valid, then do a cleanupQuery on it */
    IF VALID-HANDLE(hQuery) THEN
       cleanupQuery(hQuery).

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE enable_UI dbbr 
PROCEDURE enable_UI :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/

  DISPLAY 
      cDatabase 
      cFields 
      cTable
      lAppSrvr 
      fiWhere
      fiValue
      cOperator

    WITH FRAME DEFAULT-FRAME IN WINDOW dbbr.

  hBrowse:VISIBLE = YES.

  ENABLE 
      btn-Query
      btn-Refresh
      btn-GetTT
      cDatabase 
      cFields 
      cTable
      lAppSrvr 
      fiWhere
      fiValue
      cOperator
      RECT-1 
    WITH FRAME DEFAULT-FRAME IN WINDOW dbbr.

  VIEW dbbr.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE getBrowse dbbr 
PROCEDURE getBrowse :
/*------------------------------------------------------------------------------
  Purpose:     Populates the browse.
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
  DEFINE VARIABLE hTTBuffer AS HANDLE NO-UNDO.
  DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
  DEFINE VARIABLE hCurrField AS HANDLE NO-UNDO.
  DEFINE VARIABLE hField AS HANDLE NO-UNDO.
  DEFINE VARIABLE iCount AS INTEGER NO-UNDO.
  DEFINE VARIABLE iCount2 AS INTEGER NO-UNDO.
  DEFINE VARIABLE cSql AS CHARACTER NO-UNDO.

  DO WITH FRAME {&FRAME-NAME}:
    /* Just make sure that all the variables are in sync with the screen value */
    ASSIGN
      cDatabase
      cTable
      cFields
      fiWhere
      cOperator
      fiValue
    .

    /* Hide the browse while it is repopulated to avoid flashing */
    hBrowse:VISIBLE = NO.
    hBrowse:SENSITIVE = NO.

    /* Empty the list of Browse columns */
    cBrwsCols = "".

    /* Empty the browse (ie, get rid of the old query */
    RUN emptyBrowse.

    /* Reset the color toggle (used in rowDisplay) */
    lRow = NO.

    IF cDatabase = "<Dynamic Temp Tables>" THEN
    DO:
      hTTBuffer = WIDGET-HANDLE(cTable).
      CREATE BUFFER hBuffer FOR TABLE hTTBuffer BUFFER-NAME "Tmp".
      RUN getQuery (STRING(hBuffer),
                    "FOR EACH " + hBuffer:NAME + " NO-LOCK" , 
                    OUTPUT hQuery).
    END.
    ELSE
      /* Get a query based on this table */ 
      IF fiWhere = ""  THEN
         cSql = "FOR EACH " + cTable + " NO-LOCK".
      ELSE 
         cSql = "FOR EACH " + cTable + " NO-LOCK Where " + fiWhere + " "+ cOperator + " """ + fiValue + """".

      RUN getQuery (cDatabase + "." + cTable,cSql,OUTPUT hQuery).

    /* Attach the query to the browse */
    hBrowse:QUERY = hQuery.
    
    /* For each of the fields that were selected in the selection list, 
       add a column to the browse */
    DO iCount = 1 TO NUM-ENTRIES(cFields):
      IF cDatabase = "<Dynamic Temp Tables>" THEN
      DO:
        hCurrField = hBuffer:BUFFER-FIELD(ENTRY(iCount,cFields)).
        hField = hBrowse:ADD-LIKE-COLUMN(hCurrField).
      END.
      ELSE
        hField = hBrowse:ADD-LIKE-COLUMN(cDatabase + "." + cTable + "." + ENTRY(iCount,cFields)).

      /* Build up the list of browse columns for use in rowDisplay */
      IF VALID-HANDLE(hField) THEN
        cBrwsCols = cBrwsCols + (IF cBrwsCols = "" THEN "" ELSE ",") 
                  + STRING(hField).
    END.

    /* Now open the query */
    hQuery:QUERY-OPEN().

    /* And show the browse to the user */
    hBrowse:VISIBLE = YES.
    hBrowse:SENSITIVE = YES.
    
  END.
END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE getDatabaseList dbbr 
PROCEDURE getDatabaseList :
/*------------------------------------------------------------------------------
  Purpose:     Walks through the list of Local Database Names and creates a list
               of entries in the cDatabase combo-box.
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
  DEFINE VARIABLE iCount AS INTEGER NO-UNDO.

  /* Do with frame to avoid having to specifically scope each reference to a 
     widget in the frame. */
  DO WITH FRAME {&FRAME-NAME}:

    /* Empty the list items and set the cDatabase variable empty */
    cDatabase:LIST-ITEMS = "":U.
    cDatabase = "":U.
    
    /* Build the list of database names */
    do-blk:
    DO WHILE TRUE:
      iCount = iCount + 1.
      IF LDBNAME(iCount) <> ? AND
         LDBNAME(iCount) <> "":U THEN
          cDatabase:ADD-LAST(LDBNAME(iCount)).
      ELSE
          LEAVE do-blk.
    END.

    /* If the listTempTables function returns something, we have dynamic
       temp tables and we should add an entry to the database list */
    IF listTempTables() <> "" THEN
    DO:
      cDatabase:ADD-LAST("<Dynamic Temp Tables>").  
    END.

    /* Make sure the first item in the list is highlighted. */
    IF cDatabase:NUM-ITEMS > 0 THEN
      cDatabase = cDatabase:ENTRY(1).
    DISPLAY
        cDatabase
    .

    /* Now pretend the user changed the database. This results in the
       table list being populated. */
    APPLY "VALUE-CHANGED" TO cDatabase.
  END.


END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE getListItems dbbr 
PROCEDURE getListItems :
/*------------------------------------------------------------------------------
  Purpose:     Populates a selection list or combo-box from a dynamic query.
  Parameters:  
    icTable     - A comma-delimited list of database.tablename combinations that
                  will be used to create the buffers for the query.
    
    icForEach   - The for each statement to be used as the basis for the query.
    
    icLabel     - The field to be used as the label portion of the 
                  LIST-ITEM-PAIRS
                  
    icValue     - The field to be used as the value portion of the 
                  LIST-ITEM-PAIRS
                  
    ihListBox   - The handle to the selection-list or combo-box that is to be
                  populated             
  
------------------------------------------------------------------------------*/
  DEFINE INPUT  PARAMETER icTable AS CHARACTER  NO-UNDO.
  DEFINE INPUT PARAMETER icForEach AS CHARACTER NO-UNDO.
  DEFINE INPUT  PARAMETER icLabel AS CHARACTER  NO-UNDO.
  DEFINE INPUT PARAMETER icValue AS CHARACTER NO-UNDO.
  DEFINE INPUT PARAMETER ihListBox AS HANDLE NO-UNDO.


  DEFINE VARIABLE hTabQry AS HANDLE NO-UNDO.

  DEFINE VARIABLE cTableName AS CHARACTER NO-UNDO.
  DEFINE VARIABLE cLabel AS CHARACTER NO-UNDO.
  DEFINE VARIABLE cValue AS CHARACTER NO-UNDO.


  /* Empty the selection-list or combo-box */
  ihListBox:LIST-ITEM-PAIRS = ?.
  ihListBox:SCREEN-VALUE = "":U.

  /* Get a handle to the query that we are going to use for this */
  RUN getQuery (icTable, icForEach, OUTPUT hTabQry).

  /* Open the query and get the first record */
  hTabQry:QUERY-OPEN().
  hTabQry:GET-FIRST().


  /* Iterate through the query for all the records */
  do-blk1:
  DO WHILE NOT hTabQry:QUERY-OFF-END:

      /* Set the label variable equal to the label field value */
      cLabel = getFieldValue(icLabel,hTabQry).

      /* Set the value variable equal to the value field value */
      cValue = getFieldValue(icValue,hTabQry).

      /* Add this row to the list box */
      ihListBox:ADD-LAST(cLabel,cValue).

      /* Get the next query record */
      hTabQry:GET-NEXT().
  END.

  /* Clean up the query */
  cleanupQuery(hTabQry).
  
  /* Set the value of the list-box to be the first entry in the list */
  IF ihListBox:NUM-ITEMS > 0 THEN
    ihListBox:SCREEN-VALUE = ihListBox:ENTRY(1).

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE getQuery dbbr 
PROCEDURE getQuery :
/*------------------------------------------------------------------------------
  Purpose:     Sets up a dynamic query
  Parameters:  
    icBuffers   - A comma-delimited string of database.table names that need to 
                  be used in the query. They must specified in the order they
                  are referenced in the FOR EACH.
                  
    icForEach   - The FOR EACH statement to be used for the query.
    
    ohQuery     - The handle variable into which the handle of the query must
                  be saved.
------------------------------------------------------------------------------*/
  DEFINE INPUT PARAMETER icBuffers AS CHARACTER NO-UNDO.
  DEFINE INPUT PARAMETER icForEach AS CHARACTER  NO-UNDO.
  DEFINE OUTPUT PARAMETER ohQuery AS HANDLE NO-UNDO.
  
  DEFINE VARIABLE hTable AS HANDLE NO-UNDO.
  DEFINE VARIABLE cTableName AS CHARACTER NO-UNDO.
  DEFINE VARIABLE iCount AS INTEGER NO-UNDO.

  /* Create the query and assign the handle to the output parameter variable */
  CREATE QUERY ohQuery.

  /* Iterate through the list of table names provided in icBuffers */
  DO iCount = 1 TO NUM-ENTRIES(icBuffers):

    /* Set the table name = to this entry number */
    cTableName = ENTRY(iCount, icBuffers).

    /* Try and convert the table name to a buffer handle */
    hTable = WIDGET-HANDLE(cTableName) NO-ERROR.
    
    IF ERROR-STATUS:ERROR OR 
       NOT VALID-HANDLE(hTable) OR
       hTable:TYPE <> "BUFFER" THEN
      /* Create a buffer for this table */
      CREATE BUFFER hTable FOR TABLE cTableName.

    /* Add this buffer to the query */
    ohQuery:ADD-BUFFER(hTable).
  END.

  /* Prepare the query */
  ohQuery:QUERY-PREPARE(icForEach).

  /* At this point the procedure returns with the handle to the query that we
     created. The caller can now do what it likes with the query */

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE getTempTable dbbr 
PROCEDURE getTempTable :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
  DEFINE VARIABLE cTables AS CHARACTER NO-UNDO.
  DEFINE VARIABLE cForEach AS CHARACTER NO-UNDO.
  DEFINE VARIABLE cFields AS CHARACTER NO-UNDO.
  DEFINE VARIABLE cTableName AS CHARACTER NO-UNDO.
  DEFINE VARIABLE iRows AS INTEGER    NO-UNDO.
  DEFINE VARIABLE hAppServer AS HANDLE     NO-UNDO.
  DEFINE VARIABLE lAns AS LOGICAL NO-UNDO.
  DEFINE VARIABLE hTable AS HANDLE     NO-UNDO.

  /* Run the dialog that gets the data from the user */
  RUN hkdlgtt.w (OUTPUT cTables, OUTPUT cForEach, OUTPUT cFields, 
                    OUTPUT cTableName, OUTPUT iRows).

  /* Ignore if nothing comes back */
  IF cTables = "" THEN
    RETURN.


  /* If we this we need to, connect to the AppServer */
  IF lAppSrvr THEN
  DO:
      CREATE SERVER hAppServer.

      lAns = hAppServer:CONNECT("-H localhost -S NS1 -AppService asbroker1").
      IF NOT lAns THEN
        RETURN.
  END.
  /* Otherwise set the AppServer handle to be the SESSION handle */
  ELSE
    hAppServer = SESSION:HANDLE.

  /* Run the gettemptable procedure */
  RUN code/gettemptable.p  ON SERVER hAppServer 
     (cTables, cForEach, cFields, cTableName, iRows, OUTPUT TABLE-HANDLE hTable).

  /* If we need to, disconnect from the AppServer */
  IF lAppSrvr THEN
  DO:
    lAns = hAppServer:DISCONNECT().

    DELETE OBJECT hAppServer.
  END.

  APPLY "CHOOSE" TO btn-Refresh IN FRAME {&FRAME-NAME}.


END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE getTempTableFields dbbr 
PROCEDURE getTempTableFields :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
  DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
  DEFINE VARIABLE hField AS HANDLE NO-UNDO.
  DEFINE VARIABLE iCount AS INTEGER NO-UNDO.

  DO WITH FRAME {&FRAME-NAME}:
    /* Empty the field list */
    cFields:LIST-ITEM-PAIRS = ?.
    cFields:SCREEN-VALUE = "":U.
    
    /* Convert the table's string to a handle */
    hBuffer = WIDGET-HANDLE(cTable:SCREEN-VALUE).
    
    /* Iterate through the number of fields on the buffer */
    DO iCount = 1 TO hBuffer:NUM-FIELDS:
      /* Get the current field */
      hField = hBuffer:BUFFER-FIELD(iCount).
      /* Add the field name to the list */
      cFields:ADD-LAST(hField:NAME,hField:NAME).
    END.
  END.

END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE getTempTableList dbbr 
PROCEDURE getTempTableList :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
  DEFINE VARIABLE cTTList AS CHARACTER  NO-UNDO.
  DEFINE VARIABLE iCount AS INTEGER    NO-UNDO.
  DEFINE VARIABLE hTT AS HANDLE     NO-UNDO.
  
  /* Get the list of temp-table handles */
  cTTList = listTempTables().
  
  DO WITH FRAME {&FRAME-NAME}:
    /* Empty the Table combo box */
    cTable:LIST-ITEM-PAIRS = ?.
    cTable:SCREEN-VALUE = "":U.
    
    /* Iterate through the temp table list */
    DO iCount = 1 TO NUM-ENTRIES(cTTList):
      /* Convert this entry back to a handle */
      hTT = WIDGET-HANDLE(ENTRY(iCount,cTTList)).
      /* and add it to the table list */
      cTable:ADD-LAST(hTT:NAME,STRING(hTT:DEFAULT-BUFFER-HANDLE)).
    END.

    /* Set the value of the list-box to be the first entry in the list */
    IF cTable:NUM-ITEMS > 0 THEN
      cTable:SCREEN-VALUE = cTable:ENTRY(1).
  END.


END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE initializeObject dbbr 
PROCEDURE initializeObject :
/*------------------------------------------------------------------------------
  Purpose:     
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
  /* Create the dynamic browser here */
  CREATE BROWSE hBrowse
    ASSIGN
      X = 5
      Y = 130
      WIDTH-PIXELS = 710
      HEIGHT-PIXELS = 400
      VISIBLE = NO
      ROW-MARKERS = NO
      SEPARATORS = YES
      COLUMN-RESIZABLE = YES
      COLUMN-MOVABLE = YES
      FRAME = FRAME {&FRAME-NAME}:HANDLE

    /* The trigger clause is here to do the alternate line color in the broswe */
    TRIGGERS:
      ON ROW-DISPLAY PERSISTENT RUN rowDisplay IN THIS-PROCEDURE.
    END TRIGGERS
  .

  /* Now get the list of databases */
  RUN getDatabaseList.


END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE rowDisplay dbbr 
PROCEDURE rowDisplay :
/*------------------------------------------------------------------------------
  Purpose:     Just for fun.. The code that displays alternate line colors in
               the browse.
  Parameters:  <none>
  Notes:       
------------------------------------------------------------------------------*/
  DEFINE VARIABLE iBGColor AS INTEGER NO-UNDO.
  DEFINE VARIABLE iFGColor AS INTEGER NO-UNDO.
  DEFINE VARIABLE iCount AS INTEGER NO-UNDO.
  DEFINE VARIABLE hBrwsCol AS HANDLE NO-UNDO.
  
  /* If the toggle is yes */
  IF lRow THEN
    ASSIGN
      iBGColor = 8  /* Set the background color grey */
      iFGColor = 0  /* and foreground color black */
    .
  ELSE
    ASSIGN
      iBGColor = 15 /* else background color white */
      iFGColor = 0  /* and foreground color black */
    .

  /* Iterate through the list of browse columns */
  DO iCount = 1 TO NUM-ENTRIES(cBrwsCols):

    /* Convert the string value to a handle */
    hBrwsCol = WIDGET-HANDLE(ENTRY(iCount,cBrwsCols)).

    IF VALID-HANDLE(hBrwsCol) THEN
    DO:
      hBrwsCol:BGCOLOR = iBGColor. /* Set the cell's background color */
      hBrwsCol:FGCOLOR = iFGColor. /* and it's foreground color */
    END.
  END.
  
  lRow = NOT lRow. /* Set the toggle opposite */
END PROCEDURE.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

/* ************************  Function Implementations ***************** */

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION cleanupQuery dbbr 
FUNCTION cleanupQuery RETURNS LOGICAL
  ( INPUT hQuery AS HANDLE ) :
/*------------------------------------------------------------------------------
  Purpose:  Deletes a query object and all its buffers. 
            
    Notes:  DON'T CALL THIS WITH STATIC BUFFERS.
------------------------------------------------------------------------------*/
    DEFINE VARIABLE cBuffers AS CHARACTER NO-UNDO.
    DEFINE VARIABLE iCount AS INTEGER NO-UNDO.
    DEFINE VARIABLE hBuffer AS HANDLE     NO-UNDO.

    
    /* We're not interested if the query has invalid handle */
    IF NOT VALID-HANDLE(hQuery) THEN
        RETURN TRUE.

    /* Close the query if it is open */
    IF hQuery:IS-OPEN THEN
       hQuery:QUERY-CLOSE().

    /* We can't delete the buffers before we have deleted the query object.
       We also can't get the list of buffers that the query uses after we 
       have deleted it.
       So we're going to build up a string with all the handles to the buffers */

    /* Iterate through all the buffers and build up a string with their handles */
    DO iCount = 1 TO hQuery:NUM-BUFFERS:
      cBuffers = cBuffers + (IF cBuffers = "":U THEN "":U ELSE ",":U) 
               + STRING(hQuery:GET-BUFFER-HANDLE(iCount)).
    END.
    
    /* Now delete the query object */
    deleteObject(hQuery).

    /* Now iterate through the string */
    DO iCount = 1 TO NUM-ENTRIES(cBuffers):
      /* convert each entry to a handle */
      hBuffer = WIDGET-HANDLE(ENTRY(iCount,cBuffers)).
      /* and pass the handle to deleteObject */
      deleteObject(hBuffer).
    END.

  RETURN TRUE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION deleteObject dbbr 
FUNCTION deleteObject RETURNS LOGICAL
  ( INPUT ihObject AS HANDLE ) :
/*------------------------------------------------------------------------------
  Purpose:  Overides the standard DELETE OBJECT to check that the handle is 
            valid and prevent "Invalid Widget Handle"
    Notes:  
------------------------------------------------------------------------------*/
    
    IF VALID-HANDLE(ihObject) THEN 
      DELETE OBJECT ihObject.
    RETURN TRUE.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION getFieldValue dbbr 
FUNCTION getFieldValue RETURNS CHARACTER
  ( INPUT icField AS CHARACTER,
    INPUT ihQuery AS HANDLE ) :
/*------------------------------------------------------------------------------
  Purpose:  Takes a field name from a query and returns the value of the field.
    Notes:  
------------------------------------------------------------------------------*/
  DEFINE VARIABLE hTable AS HANDLE     NO-UNDO.
  DEFINE VARIABLE hField AS HANDLE     NO-UNDO.
  DEFINE VARIABLE iCount AS INTEGER    NO-UNDO.
  DEFINE VARIABLE cField AS CHARACTER  NO-UNDO.
  DEFINE VARIABLE cTable AS CHARACTER  NO-UNDO.
  DEFINE VARIABLE cValue AS CHARACTER  NO-UNDO.

  cValue = ?.

  /* The user may have specified the field name qualified with database and/or
     table name, so lets break this down into the right components. 
     This function actually doesn't work unless you qualify it with at least 
     the table name */ 
  CASE NUM-ENTRIES(icField,"."):
      WHEN 1 THEN
          cField = icField.
      WHEN 2 THEN
      DO:
          cField = ENTRY(2,icField,".").
          cTable = ENTRY(1,icField,".").
      END.
      WHEN 3 THEN
      DO:
          cField = ENTRY(3,icField,".").
          cTable = ENTRY(2,icField,".").
      END.
  END.

  /* Now iterate through the buffers for the query */
  do-blk3:
  DO iCount = 1 TO ihQuery:NUM-BUFFERS:
      hTable = ihQuery:GET-BUFFER-HANDLE(iCount).

      /* If the buffer's table name matches the one passed in */
      IF hTable:NAME = cTable THEN
      DO:
        /* set the field handle to the buffer-field for this field*/
        hField = hTable:BUFFER-FIELD(cField).
        /* and set the output variable to the value of the buffer-field */
        cValue = hField:BUFFER-VALUE.
        /* and get out of the do block */
        LEAVE do-blk3.
      END.
  END.

  RETURN cValue.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

&ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION listTempTables dbbr 
FUNCTION listTempTables RETURNS CHARACTER
  ( /* parameter-definitions */ ) :
/*------------------------------------------------------------------------------
  Purpose:  
    Notes:  
------------------------------------------------------------------------------*/
  DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
  DEFINE VARIABLE cHandle AS CHARACTER  NO-UNDO.
  DEFINE VARIABLE cList AS CHARACTER NO-UNDO.
  DEFINE VARIABLE iCount AS INTEGER    NO-UNDO.

  hBuffer = SESSION:FIRST-BUFFER.

  /* Walk through the list of buffers that belong to Dynamic Tenp Tables*/
  do-ltt:
  DO WHILE VALID-HANDLE(hBuffer) AND VALID-HANDLE(hBuffer:TABLE-HANDLE):
    /* Convert the handle to a string */
    cHandle = STRING(hBuffer:TABLE-HANDLE).
    
    /* If the handle is not in cList, add it */
    IF NOT CAN-DO(cList,cHandle) THEN
      cList = cList + (IF cList = "" THEN "" ELSE ",")
            + cHandle.

    /* Go on to the next Buffer */
    hBuffer = hBuffer:NEXT-SIBLING.
  END.

  RETURN cList.   /* Function return value. */

END FUNCTION.

/* _UIB-CODE-BLOCK-END */
&ANALYZE-RESUME

